﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OfficeOpenXml;
using System.Diagnostics;

namespace AWA.VAT.DbAccess.Excel
{
    public class ExcelDataConnector : ExcelBase, IDataConnector
    {
        public ExcelDataConnector(string fileLocation)
        {
            this.FileLocation = fileLocation;
        }

        public IEnumerable<PersonInfo> GetInfo()
        {
            Dictionary<string, PersonInfo> people = new Dictionary<string, PersonInfo>();

            people.Add("julian_tempest@yahoo.com", new PersonInfo()
            {
                RealName = "Lucas DeStevens",
                Email = "julian_tempest@yahoo.com",
                StreetAddress = "5881 Sardis Church Rd.",
                City = "Macon",
                State = "GA",
                ZIP = "31210",
                Country = "United States",
                Sent = false
            });
            people.Add("jingoro@jingoro.net", new PersonInfo()
            {
                RealName = "Daric Jackson",
                Email = "jingoro@jingoro.net",
                StreetAddress = "123 Test Street",
                City = "Testville",
                State = "TE",
                ZIP = "12345",
                Country = "Testocracy of the Test",
                Sent = false
            });

            // open excel sheet
            ExcelPackage excel = new ExcelPackage(new System.IO.FileInfo(this.FileLocation));
            ExcelWorksheet ws = excel.Workbook.Worksheets[_tabName];
            for (int i = _firstRow; i <= _lastRow; i++) // TODO: Dynamically check for first, last
            {
                string email = ws.Cells[EmailCell + i.ToString()].Text;

                if (people.ContainsKey(email))
                    continue;

                string name = ws.Cells[RealNameCell + i.ToString()].Text;
                
                string street = ws.Cells[StreetAddressCell + i.ToString()].Text;
                string city = ws.Cells[CityCell + i.ToString()].Text;
                string state = ws.Cells[StateCell + i.ToString()].Text;
                string zip = ws.Cells[ZIPCell + i.ToString()].Text;
                string country = ws.Cells[CountryCell + i.ToString()].Text;
                bool sent = ws.Cells[EmailSentCell + i.ToString()].Text.ToLower()[0] == 'y';

                people.Add(email, new PersonInfo()
                    {
                        RealName = name,
                        Email = email,
                        StreetAddress = street,
                        City = city,
                        State = state,
                        ZIP = zip,
                        Country = country,
                        Sent = sent
                    });

                //Debug.WriteLine(String.Format("{0}\n{1}\n{2}", email, name, address));
            }

            excel.Dispose();

            return people.Values;
        }

        //private void PopulateColumns(ExcelWorksheet ws)
        //{
        //    throw new NotImplementedException();

        //    ExcelRangeBase range = ws.Cells.First(a => a.Text == "contest");
        //    string colRow = range.Address;
        //}

        private int GetRow(string cellAddress)
        {
            // parse off the end numbers
            throw new NotImplementedException();
        }

        private string GetCol(string cellAddress)
        {
            // parse off the beginning characters
            throw new NotImplementedException();
        }

        public void MarkAsSent(string emailAddress)
        {
            ExcelPackage excel = new ExcelPackage(new System.IO.FileInfo(this.FileLocation));
            ExcelWorksheet ws = excel.Workbook.Worksheets[_tabName];

            for (int i = _firstRow; i <= _lastRow; i++)  // TODO: Dynamically check for first, last
            {
                if (ws.Cells[EmailCell + i.ToString()].Text != emailAddress)
                    continue;
                
                ws.Cells[EmailSentCell + i.ToString()].Value = "Yes";
            }
        }

        public void MarkAsResponded(string emailAddress)
        {
            ExcelPackage excel = new ExcelPackage(new System.IO.FileInfo(this.FileLocation));
            ExcelWorksheet ws = excel.Workbook.Worksheets[_tabName];

            for (int i = _firstRow; i <= _lastRow; i++)  // TODO: Dynamically check for first, last
            {
                if (ws.Cells[RespondedCell + i.ToString()].Text != emailAddress)
                    continue;

                ws.Cells[RespondedCell + i.ToString()].Value = "Yes";
            }
        }

        public void UpdatePhysicalAddress(string emailAddress,
            string streetAddress,
            string city,
            string state,
            string zip,
            string country)
        {
            ExcelPackage excel = new ExcelPackage(new System.IO.FileInfo(this.FileLocation));
            ExcelWorksheet ws = excel.Workbook.Worksheets[_tabName];
            for (int i = _firstRow; i <= _lastRow; i++) // TODO: Dynamically check for first, last
            {
                string email = ws.Cells[EmailCell + i.ToString()].Text;

                if (email.ToLower() != emailAddress.ToLower())
                    continue;

                ws.Cells[StreetAddressCell + i.ToString()].Value = streetAddress;
                ws.Cells[CityCell + i.ToString()].Value = city;
                ws.Cells[StateCell + i.ToString()].Value = state;
                ws.Cells[ZIPCell + i.ToString()].Value = zip;
                ws.Cells[CountryCell + i.ToString()].Value = country;
            }
        }

        public void UpdateEmailAddress(string realName,
            string emailAddress)
        {
            ExcelPackage excel = new ExcelPackage(new System.IO.FileInfo(this.FileLocation));
            ExcelWorksheet ws = excel.Workbook.Worksheets[_tabName];
            for (int i = _firstRow; i <= _lastRow; i++) // TODO: Dynamically check for first, last
            {
                string name = ws.Cells[RealNameCell + i.ToString()].Text;

                if (realName.ToLower() != name.ToLower())
                    continue;

                ws.Cells[EmailCell + i.ToString()].Value = emailAddress;
            }
        }
    }
}
